Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

Avoid array fields

One particular characteristic of the OpenEdge® database not shared by SQL, or by most other modern database types, is the array field. It is best never to use arrays in your database definition. In a relational database, the use of array fields is not recommended. You might try using array fields to increase performance by reducing the number of related records needed to calculate some information. However, you are likely miscalculating the actual cost of using this technique. Consider that array fields are inherently nonnormalized—they attempt to represent a one-to-many relationship in a single record. Arriving at the appropriate extent for an array is often an arbitrary decision. If the original extent turns out later to be insufficient, you must make a schema change, and probably change your application code, to deal with this issue.

Arrays can lead to large records that span multiple database blocks. This increases the number of reads required to retrieve a single record and reduces any performance gained by using them. Transporting such large records across a network requires increased bandwidth and decreases performance. Array values cannot be efficiently indexed, cannot be used as a join field, and cannot be accessed using SQL syntax (for example, from a reporting tool that will want to report on the data those array fields hold). You cannot filter array values; that is, you cannot write a single query statement to retrieve records where one of the values in an array field matches a filtering condition. These are all good reasons to avoid arrays.

Given the problems you might encounter, you should use arrays only for some very particular purpose on the server-side of your application. If an array is necessary, use it for data that is only accessed in your business logic. The data cannot be used in any way outside the server-side procedure where it is manipulated. Standard Progress Dynamics components cannot display array data. You cannot manipulate the data on the client, sort or filter on it, or report on it.

If you define a SmartDataObject™ for a table with an array field, the array is expanded into a series of discrete fields for each element. Usually, this does not result in the kind of client-side representation that you want for the data. The statement defining the temp-table that passes data between the client and server might easily exceed the maximum length of a Progress 4GL statement. That would effectively make the table unusable with Progress Dynamics.

Note: The framework converts imported array fields into fields with ordinal numbers appended. Address[ ] becomes Address1, Address2, Address3, and so on. This can lead to conflicts if you already have similarly named fields in your database.